﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.IO;

// Sample :
// http://srv:88/ws/CheckPointsData.aspx?shop=%D0%91%D0%B0%D0%BB%D0%B0%D1%88%D0%B8%D1%85%D0%B0&Year=2011&Month=12
public partial class ws_CheckPointsData : System.Web.UI.Page
{
    string ConnStr = GetAppSetting("1CConnect");

    protected void Page_Load(object sender, EventArgs e)
    {
        string ShopName, strYear, strMonth;
        if (Request.Params["shop"] != null)
            ShopName = Request.Params["shop"].ToString();
        else
            ShopName = "-";

        if (Request.Params["Year"] != null)
            strYear = Request.Params["Year"].ToString();
        else
            strYear = "2011";

        if (Request.Params["Month"] != null)
            strMonth = Request.Params["Month"].ToString();
        else
            strMonth = "11";

        XmlDocument xReturn = getXML(ShopName, Convert.ToInt32(strYear), Convert.ToInt32(strMonth));
        Response.ContentType = "text/xml";
        Response.Write(xReturn.OuterXml);
    }

    public XmlDocument getXML(string ShopName, int Year, int Month)
    {
        XmlDocument xReturn = new XmlDocument();
        DataTable tbl = new DataTable("tbl");
        Exception err = GetTbl(ref tbl, ConnStr, ShopName, Year, Month);

        if (err == null)
        {
            tbl.Columns[0].ColumnName = "Day";
            tbl.Columns[0].Caption = "Day";
            tbl.Columns[1].ColumnName = "RealIn";
            tbl.Columns[1].Caption = "RealIn";
        }
        else
        {
            tbl.Columns.Add("Day", typeof(int));
            tbl.Columns[0].Caption = "Day";
            tbl.Columns.Add("RealIn", typeof(int));
            tbl.Columns[1].Caption = "RealIn";

            DataRow r = tbl.NewRow();
            r[0] = -1;
            r[1] = -1;
            tbl.Rows.Add(r);
        }

        using (StringWriter stringWriter = new StringWriter())
        {
            using (XmlTextWriter xmlTextwriter = new XmlTextWriter(stringWriter))
            {
                tbl.WriteXml(xmlTextwriter, XmlWriteMode.WriteSchema);
                //XmlNode xRoot = xReturn.CreateElement("root");
                //xReturn.AppendChild(xRoot);
                xReturn.LoadXml(stringWriter.ToString());
            }
        }

        return xReturn;
    }

    Exception GetTbl(ref DataTable tbl, string parConnStr, string Point, int Year, int Month)
    {
        Exception ret = null;
        string connStr = GetAppSetting("1CConnect");
        connStr = connStr.Replace("Zar_kor_81", "RetailStat");
        string sql1 =
"SELECT DAY(t.DateCreated) as Day,SUM(t.RealIn) as RealIn FROM Shops s "
+ "INNER JOIN Entries e ON e.ShopID=s.ID INNER JOIN Sensors n ON n.EntryID=e.ID "
+ "INNER JOIN Traffic t ON t.SensorID=n.ID WHERE "
+ "YEAR(t.DateCreated)=" + Year.ToString() + " AND MONTH(t.DateCreated)=" + Month.ToString()
+ " AND s.Name='" + Point + "'"
+ " GROUP BY s.[Name], DAY(t.DateCreated) ";

        try
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(sql1, conn))
                {
                    using (OleDbDataAdapter a = new OleDbDataAdapter(cmd))
                    {
                        if (tbl == null)
                            tbl = new DataTable();
                        a.Fill(tbl);
                    }
                }
            }
        }
        catch (Exception err)
        {
            ret = new Exception(err.Message, err);
        }
        return ret;
    }

    static public string GetAppSetting(String mName)
    {
        String vStr = null;
        string strPath =
    @"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(strPath);
        XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
        foreach (XmlNode keynode in node.ChildNodes)
        {
            string keyStr = keynode.Attributes["key"].Value;
            if (keyStr == mName)
            {
                vStr = keynode.Attributes["value"].Value;
                break;
            }
        }

        if (String.IsNullOrEmpty(vStr))
            vStr = "";

        return vStr;
    }

}